Query Data

This activity retrieves rows of data from a database and makes the data available as tokens.

Note: This activity produces a token for each column in the specified table. The tokens for the first row the query finds are available to other activities in the workflow. If you want to access tokens for additional rows, use a For Each Row activity.

How does this activity look in the Designer Pane?

To add this activity to a workflow definition 

• Drag it from the Toolbox Pane and drop it in the Designer Pane.

To configure this activity

Select the activity in the Designer Pane to configure the following property boxes in the Properties Pane.

• Activity Name

  Once added to a workflow definition, the default name of an activity can be changed. Providing a custom name for an activity helps you remember the role it plays.

  To name an activity

  1. Add an activity to your workflow by dragging it from the Toolbox Pane and dropping it in the Designer Pane.
  2. Select the activity in the Designer Pane.
  3. Under Activity Name in the Properties Pane, replace the default name.

  

  Note: Activity names cannot be the same as any other activity name in the workflow, they cannot be the same as the workflow's name, they must be less than 100 characters, they must contain at least one alphanumeric character, they cannot be "Name," and they cannot be the same as the activity's runtime type (which is usually only an issue with custom activities).

• Activity Description

  Use the Activity Description to provide descriptive text to help you remember the role that the activity plays in the workflow. All activities contain a default description that you can modify while constructing your workflow.

  To modify an activity description

  1. Add an activity to your workflow by dragging it from the Toolbox Pane and dropping it in the Designer Pane.
  2. Select the activity in the Designer Pane.
  3. Under Activity Description in the Properties Pane, replace the default description.

  

• Data Source

  This property box determines which data source the activity will use.

  To select a data source

  1. Add a Custom Query, Insert Data, Query Data, or Update Data activity to your workflow definition by dragging it from the Toolbox Pane and dropping it in the Designer Pane.
  2. Select the activity in the Designer Pane.
  3. Under Data Source in the Properties Pane, select a data source from the drop-down menu.

  

  Note: Because the Workflow Server performs the data-source queries, you must create all data sources on the Workflow Server. Services cannot access mapped drives when you are setting up the data source.

  4. Optional: To add, configure, remove, or test a connection to an external data source, select Manage Data Sources from the drop-down menu. More information.
  5. Optional: Click the refresh icon to update the list of data sources available in the drop-down menu.
• Query Criteria

  This property box determines which table the Query Data activity will search and which row's values the activity will return as tokens. This activity will return the values of the first row it finds as tokens. It also will return the collection of rows that have the specified column values.

  Note: This activity produces a token for each column in the specified table. The tokens for the first row the query finds are available to other activities in the workflow. If you want to access tokens for additional rows, use a For Each Row activity.

  Note: You must configure the Data Source property box before configuring this property box.

  To configure Query Criteria

  1. Add the Query Data activity to your workflow definition by dragging it from the Toolbox Pane and dropping it in the Designer Pane.
  2. Select the activity in the Designer Pane.
  3. Under Query Criteria in the Properties Pane, select the table you want to query from the drop-down menu.

  

  To Add a Query

  1. Click Add in the Query Criteria property box.
  2. In the Add Query Criteria dialog box, select the column you want to query from the Column drop-down menu.

  

  3. Next to Value, type in a value to search for in that column, or click the Token button (right arrow) to use tokens.

  Tip: If you want a token to be replaced by the null value in the case that the token's value is blank (the empty string), apply the NULL token formatting expression. If you want to query a column for the null value, insert the NULL token: %(DB.NULL).

  4. Click OK.

  To Edit a Query

  1. Click an existing query in the Query Criteria property box.
  2. Click Edit.
  3. In the Edit Query Criteria dialog box, modify the search value. Click the Token button (right arrow) to use tokens.
  4. Click OK.

  To Remove a Query

  1. Click an existing query in the Query Criteria property box.
  2. Click Remove.
  4. Click the Test Query link to test your query.
  1. If prompted, in the Define Query Parameters dialog box, insert the value you want to test.
  2. Click OK.
  3. The Test Query dialog box will display how many rows the query will return or if there are any errors.
  4. Click OK.
• Rows to Return

  This property box determines how many rows the activity will return.

  Note: If you chose to have these activities return more than one row, you can access the token values for the additional rows with the For Each Row activity.

  To configure Rows to Return

  1. Add the Query Data or Custom Query activity to your workflow definition by dragging it from the Toolbox Pane and dropping it in the Designer Pane.
  2. Select the activity in the Designer Pane.
  3. Under Rows to Return in the Properties Pane, specify how many rows you want returned.

  

  • First row only: Returns the first row in the database that contains the queried value. The database is searched from the top down.
  • First ___ rows: Returns the first specified number of rows that contain the queried value. Type in the desired number of rows, or use the scroll box (up-and-down arrows) to select a number of rows. Alternatively, click the Token button (right arrow) to use tokens. The database is searched from the top down.
  • All Rows: Returns all rows that contain the queried value.
• Query Options

  This property box allows you to specify a custom timeout for data query activities.

  To specify a custom timeout

  1. Add a Custom Query, Insert Data, Query Data, or Update Data activity to your workflow definition by dragging it from the Toolbox Pane and dropping it in the Designer Pane.
  2. Select the activity in the Designer Pane.
  3. Under Query Options in the Properties Pane, select the Specify a custom timeout check box and specify the desired timeout value in seconds.

     

  Note: The default timeout value is 30 seconds.